package database_layer;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import processing_layer.Course;
import processing_layer.CourseSession;
import processing_layer.User;

/**
 * This class handles all the queries which alter the data in the DB.
 * It contains all the INSERT, UPDATE and DELETE queries needed for the web service
 * to run.
 * 
 * The isolation level used here is REPEATABLE READ, with auto commit off.
 * Since some of the queries are complex, and some of the transactions may contain
 * several altering-state queries, there's a need for a rollbacking mechanism here.
 * 
 * We decided to use the REPEATABLE_READ isolation level since it receives a snapshot
 * of the database at the beginning of the transaction, so no "new values" may be
 * seen between the queries.
 * 
 * We also used the FOR_UPDATE and LOCK_IN_SHARE_MODE commands in some of the queries
 * to add protection / locking for the lines altered. Since we decided on INNODB, we
 * can use row-level locking, and avoid locking entire tables.
 *
 */
public final class UpdateDispatcher
{
	public static int addUser(User newUser)
	{		
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet queryResult = null;
		
		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_REPEATABLE_READ, false);
			/* Checking if the user exists */
			statement = con.prepareStatement("SELECT * FROM " + Common.usersTable + " WHERE " + 
										     Common.usernameField + "=? LOCK IN SHARE MODE");
			statement.setString(1, newUser.username);
			DatabaseManager.testPrint(statement.toString());
			queryResult = statement.executeQuery();
			
			if (queryResult.next())
			{
				/* User already exists */
				DatabaseManager.closeQuery(con, statement, queryResult);
				return Common.ENTRY_EXISTS;
			}
			
			statement = con.prepareStatement("INSERT INTO " + Common.usersTable + " (" + 
											 Common.usernameField + "," +
											 Common.passwordField + "," +
											 Common.firstNameField + "," +
											 Common.lastNameField + "," +
											 Common.phoneField + "," +
											 Common.adminField + "," +
											 Common.addressField + "," +
											 Common.photoField + ") VALUES(?,?,?,?,?,?,?,?)");
			statement.setString(1, newUser.username);
			statement.setString(2, newUser.password);
			statement.setString(3, newUser.firstName);
			statement.setString(4, newUser.lastName);
			statement.setString(5, newUser.phone);
			statement.setBoolean(6, newUser.isAdmin);
			statement.setString(7, newUser.address);
			statement.setBlob(8, newUser.photo);
			DatabaseManager.testPrint(statement.toString());
			statement.execute();
			con.commit();
		}
		
		catch(SQLException e)
		{
			DatabaseManager.performRollback(con);
			return Common.INTERNAL_DB_ERROR;
		}
		
		finally
		{
			DatabaseManager.closeQuery(con, statement, queryResult);
		}
		
		return Common.NO_ERROR;
	}
	
	public static int updateUserDetails(String username, HashMap<String, String> detailsToUpdate)
	{		
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet queryResult = null;

		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_REPEATABLE_READ, false);
			/* Locking the user entry */
			statement = con.prepareStatement("SELECT * FROM " + Common.usersTable + " WHERE " + 
				     						 Common.usernameField + "=? LOCK IN SHARE MODE");
			statement.setString(1, username);
			DatabaseManager.testPrint(statement.toString());
			queryResult = statement.executeQuery();
			
			if (!queryResult.next())
			{
				/* Entry doesn't even exist */
				DatabaseManager.closeQuery(con, statement, queryResult);
				return Common.ENTRY_DOESNT_EXIST;
			}
			
			String statementString = "UPDATE " + Common.usersTable + " SET ";

			int index = 1;
			/* Inserting the field names */
			for (String key : detailsToUpdate.keySet())
			{
				statementString += key + "=?";
				if (index != detailsToUpdate.size())
				{
					statementString += ",";
				}
				index++;
			}
			
			statementString += " WHERE " + Common.usernameField + "=?";
						
			statement = con.prepareStatement(statementString);
			
			index = 1;
			/* Inserting the values */
			for (String key : detailsToUpdate.keySet())
			{
				statement.setString(index, detailsToUpdate.get(key));
				index++;
			}
			
			/* Last field is the username */
			statement.setString(index, username);
			DatabaseManager.testPrint(statement.toString());		
			statement.execute();
			con.commit();
		}
		catch(SQLException e)
		{
			DatabaseManager.performRollback(con);
			return Common.INTERNAL_DB_ERROR;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, queryResult);
		}	
		
		return Common.NO_ERROR;
	}
	
	public static int updateUserImage(String username, Blob image)
	{
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet queryResult = null;

		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_REPEATABLE_READ, false);
			/* Locking the user entry */
			statement = con.prepareStatement("SELECT * FROM " + Common.usersTable + " WHERE " + 
				     						 Common.usernameField + "=? LOCK IN SHARE MODE");
			statement.setString(1, username);
			DatabaseManager.testPrint(statement.toString());
			queryResult = statement.executeQuery();
			
			if (!queryResult.next())
			{
				/* Entry doesn't even exist */
				DatabaseManager.closeQuery(con, statement, queryResult);
				return Common.ENTRY_DOESNT_EXIST;
			}
			
			statement = con.prepareStatement("UPDATE " + Common.usersTable + " SET " +
											 Common.photoField + "=? WHERE " + Common.usernameField + "=?");
			
			statement.setBlob(1, image);
			statement.setString(2, username);
			DatabaseManager.testPrint(statement.toString());		
			statement.execute();
			con.commit();
		}
		catch(SQLException e)
		{
			DatabaseManager.performRollback(con);
			return Common.INTERNAL_DB_ERROR;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, queryResult);
		}	
		
		return Common.NO_ERROR;		
	}
	public static int removeUser(String username)
	{		
		Connection con = null;
		PreparedStatement statement = null;
		
		try
		{		
			con = DatabaseManager.getConnection(Connection.TRANSACTION_REPEATABLE_READ, false);
			/* Locking the row prior to removing it */
			statement = con.prepareStatement("SELECT * FROM " + Common.usersTable + " WHERE " + 
										     Common.usernameField + "=? LOCK IN SHARE MODE");

			statement.setString(1, username);
			DatabaseManager.testPrint(statement.toString());
			statement.execute();
			
			/* Locking the course tables, to update the number of registered */
			statement = con.prepareStatement("SELECT * FROM " + Common.coursesTable + " AS c WHERE EXISTS " +
											 "(SELECT * FROM " + Common.registeredTable + " AS r WHERE " +
											 "c." + Common.courseNameField + "=r." + Common.courseNameField + 
											 " AND r." + Common.usernameField + "=?) LOCK IN SHARE MODE");
			statement.setString(1, username);
			DatabaseManager.testPrint(statement.toString());
			statement.execute();
			
			/* Locking the rows to prevent others from checking how much are registered */
			statement = con.prepareStatement("SELECT * FROM " + Common.registeredTable + 
					                         " WHERE " + Common.usernameField + "=? FOR UPDATE");
			statement.setString(1, username);
			DatabaseManager.testPrint(statement.toString());
			statement.execute();
			
			/* Removing a registration space from all the courses */
			statement = con.prepareStatement("UPDATE " + Common.coursesTable + " AS c SET c." + Common.alreadyRegisteredField + "=c." +
					 Common.alreadyRegisteredField + " - 1 WHERE EXISTS " +
					 "(SELECT * FROM " + Common.registeredTable + " AS r WHERE " +
					 "c." + Common.courseNameField + "=r." + Common.courseNameField + 
					 " AND r." + Common.usernameField + "=?)");
			statement.setString(1, username);
			DatabaseManager.testPrint(statement.toString());
			statement.execute();

			/* Removing the user itself, the foreign keys will take care of everything... */
			statement = con.prepareStatement("DELETE FROM " + Common.usersTable + " WHERE " + Common.usernameField + "=?");
			statement.setString(1, username);
			DatabaseManager.testPrint(statement.toString());		
			statement.execute();
			con.commit();
		}
		catch(SQLException e)
		{
			DatabaseManager.performRollback(con);
			return Common.INTERNAL_DB_ERROR;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, null);
		}
		
		return Common.NO_ERROR;
	}
	
	public static int registerUserToCourse(String username, String courseName)
	{
		
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet queryResult = null;
		
		try
		{	
			con = DatabaseManager.getConnection(Connection.TRANSACTION_REPEATABLE_READ, false);
			/* Locking the course row for a shared read, it will prevent from deleting it */
			statement = con.prepareStatement("SELECT * FROM " + Common.coursesTable + " WHERE " +
											 Common.courseNameField + "=?");
			statement.setString(1, courseName);
			DatabaseManager.testPrint(statement.toString());		
			queryResult = statement.executeQuery();
			
			if (!queryResult.next())
			{
				DatabaseManager.closeQuery(con, statement, queryResult);
				return Common.ENTRY_DOESNT_EXIST;
			}
			
			/* Pre-condition strings */
			
			/* One ? [AND] */
			String notRegisteredTo = "NOT EXISTS (SELECT * FROM " + Common.registeredTable + " AS r WHERE " +
								     "r." + Common.courseNameField + "=c." + Common.courseNameField + " AND r." + Common.usernameField + "=?)";
			
			/* No ? [AND] */
			String thereIsRoom = "c." + Common.capacityField + " > c." + Common.alreadyRegisteredField;
			
			/* One ? [AND, OR with the one below]*/
			String registeredToGroup = "(EXISTS (SELECT DISTINCT " + Common.groupNumberField + " FROM " + 
			                           Common.registeredTable + " AS r2 INNER JOIN " +
			                           Common.coursesTable + " AS c2 ON r2." + Common.courseNameField + "=" +
			                           "c2." + Common.courseNameField + " WHERE c." + Common.groupNumberField + 
			                           "=c2." + Common.groupNumberField + " AND r2." + Common.usernameField + "=?))";
			
			/* One ? [AND, OR with the one above*/
			String notMaxGroupNumber = "(" + Common.MAX_GROUPS_FOR_USER + " > (SELECT COUNT(DISTINCT " + Common.groupNumberField + ") FROM " + 
			                           Common.registeredTable + " AS r3 INNER JOIN " +
                                       Common.coursesTable + " AS c3 ON r3." + Common.courseNameField + "=" +
                                       "c3." + Common.courseNameField + " WHERE r3." + Common.usernameField + "=?))";
			/* One ? [AND] */
			String noOverlap = "NOT EXISTS (SELECT * FROM " + Common.sessionsTable + " AS s WHERE s." + Common.courseNameField +
							   "=c." + Common.courseNameField + " AND EXISTS " +
							   "(SELECT * FROM " + Common.coursesTable + " as c4 INNER JOIN " + Common.sessionsTable +
							   " AS s2 ON c4." + Common.courseNameField + "=s2." + Common.courseNameField + " INNER JOIN " +
							   Common.registeredTable + " AS r4 ON c4." + Common.courseNameField + "=r4." + Common.courseNameField +
							   " WHERE r4." + Common.usernameField + "=? AND s." + Common.dayField + "=s2." + Common.dayField + " AND " +
							   "((s." + Common.startingTimeField + "> s2." + Common.startingTimeField + " AND s." + Common.startingTimeField + 
							   "< s2." + Common.endingTimeField + ") OR " +
							   "(s." + Common.endingTimeField + "> s2." + Common.startingTimeField + " AND s." + Common.endingTimeField + 
								"< s2." + Common.endingTimeField + ") OR " + 
								"(s2." + Common.startingTimeField + "> s." + Common.startingTimeField + " AND s2." + Common.startingTimeField + 
								"< s." + Common.endingTimeField + ") OR " + 
								"(s2." + Common.endingTimeField + "> s." + Common.startingTimeField + " AND s2." + Common.endingTimeField + 
								"< s." + Common.endingTimeField + "))))";
							   
							   
			statement = con.prepareStatement("SELECT * FROM " + Common.coursesTable + " AS c WHERE " +
											 "c." + Common.courseNameField + "=? AND " + notRegisteredTo + " AND " + thereIsRoom + " AND " + 
											 noOverlap + " AND (" + registeredToGroup + " OR " + notMaxGroupNumber + ")");
			statement.setString(1, courseName);
			statement.setString(2, username);
			statement.setString(3, username);
			statement.setString(4, username);
			statement.setString(5, username);
			DatabaseManager.testPrint(statement.toString());		
			queryResult = statement.executeQuery();
			
			if (!queryResult.next())
			{
				DatabaseManager.closeQuery(con, statement, queryResult);
				return Common.OVERLAPPING_ENTRIES;
			}			
			
			/* We can safely register */
			statement = con.prepareStatement("INSERT INTO " + Common.registeredTable + "(" +
											 Common.usernameField + "," +
											 Common.courseNameField + ") VALUES (?,?)");
			statement.setString(1, username);
			statement.setString(2, courseName);
			DatabaseManager.testPrint(statement.toString());
			statement.execute();
			
			statement = con.prepareStatement("UPDATE " + Common.coursesTable + " SET " + 
									         Common.alreadyRegisteredField + "=" + 
									         Common.alreadyRegisteredField + "+1 WHERE " +
									         Common.courseNameField + "=?");
			statement.setString(1, courseName);
			DatabaseManager.testPrint(statement.toString());
			statement.execute();

			con.commit();
		}
		
		catch(SQLException e)
		{
			DatabaseManager.performRollback(con);
			return Common.INTERNAL_DB_ERROR;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, queryResult);
		}		
		
		return Common.NO_ERROR;
	}
	
	public static int unregisterUserFromCourse(String username, String courseName)
	{		
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet queryResult = null;
		
		try
		{	
			con = DatabaseManager.getConnection(Connection.TRANSACTION_REPEATABLE_READ, false);
			/* Locking the entries */
			statement = con.prepareStatement("SELECT * FROM " + Common.registeredTable + " WHERE " +
											 Common.usernameField +"=? AND " + Common.courseNameField + 
											 "=? FOR UPDATE");
			statement.setString(1, username);
			statement.setString(2, courseName);
			DatabaseManager.testPrint(statement.toString());
			queryResult = statement.executeQuery();
			
			if (!queryResult.next())
			{
				/* Entry doesn't exist */
				DatabaseManager.closeQuery(con, statement, queryResult);
				return Common.ENTRY_DOESNT_EXIST;
			}
			
			/* Locking the course row for a shared read, it will prevent from deleting it */
			statement = con.prepareStatement("SELECT * FROM " + Common.coursesTable + " WHERE " +
											 Common.courseNameField + "=?");
			statement.setString(1, courseName);
			DatabaseManager.testPrint(statement.toString());
			queryResult = statement.executeQuery();
			
			if (!queryResult.next())
			{
				DatabaseManager.closeQuery(con, statement, queryResult);
				return Common.ENTRY_DOESNT_EXIST;
			}
			
			/* Removing the user's registrations */
			statement = con.prepareStatement("DELETE FROM " + Common.registeredTable + " WHERE " +
					 					     Common.usernameField +"=? AND " + Common.courseNameField + "=?");
			statement.setString(1, username);
			statement.setString(2, courseName);
			DatabaseManager.testPrint(statement.toString());
			statement.execute();
			
			statement = con.prepareStatement("UPDATE " + Common.coursesTable + " SET " + 
			         Common.alreadyRegisteredField + "=" + 
			         Common.alreadyRegisteredField + "-1 WHERE " +
			         Common.courseNameField + "=?");
			statement.setString(1, courseName);
			DatabaseManager.testPrint(statement.toString());
			statement.execute();			
			
			con.commit();
		}
		catch(SQLException e)
		{
			DatabaseManager.performRollback(con);
			return Common.INTERNAL_DB_ERROR;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, queryResult);
		}		
		
		return Common.NO_ERROR;
	}
	
	public static int addCourseAndSessions(Course newCourse, ArrayList<CourseSession> sessions)
	{
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet queryResult = null;
		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_REPEATABLE_READ, false);
			/* Putting a lock (non-exclusive) on the row, to avoid
			 * it's deletion / to check if it exists.
			 */
			statement = con.prepareStatement("SELECT * FROM " + Common.coursesTable + " WHERE " +
										     Common.courseNameField + "=? LOCK IN SHARE MODE");
			statement.setString(1, newCourse.name);
			DatabaseManager.testPrint(statement.toString());
			queryResult = statement.executeQuery();
			if (queryResult.next())
			{
				/* Course exists... */
				DatabaseManager.closeQuery(con, statement, queryResult);
				return Common.ENTRY_EXISTS;
			}
						
			/* Checking if the sessions can be inserted */
			for (int i = 0 ; i < sessions.size() ; i++)
			{
				/* Checking for overlapping sessions */
				statement = con.prepareStatement("SELECT * FROM " + Common.sessionsTable + " AS s WHERE " +
				                                 Common.groupNumberField + "=? AND s." + Common.dayField + "=? AND" +
				  							     "((s." + Common.startingTimeField + "> ? AND s." + Common.startingTimeField + "< ?) OR " +
											     "(s." + Common.endingTimeField + "> ? AND s." + Common.endingTimeField + "< ?) OR " + 
												 "(? > s." + Common.startingTimeField + " AND ? < s." + Common.endingTimeField + ") OR " + 
												 "(? > s." + Common.startingTimeField + " AND ? < s." + Common.endingTimeField + "))");			                                 
				statement.setInt(1, sessions.get(i).groupNumber);
				statement.setInt(2, sessions.get(i).day);
				statement.setInt(3, sessions.get(i).startingTime);
				statement.setInt(4, sessions.get(i).endingTime);
				statement.setInt(5, sessions.get(i).startingTime);
				statement.setInt(6, sessions.get(i).endingTime);
				statement.setInt(7, sessions.get(i).startingTime);
				statement.setInt(8, sessions.get(i).startingTime);
				statement.setInt(9, sessions.get(i).endingTime);
				statement.setInt(10, sessions.get(i).endingTime);
				DatabaseManager.testPrint(statement.toString());
				queryResult = statement.executeQuery();
				if (queryResult.next())
				{
					DatabaseManager.closeQuery(con, statement, queryResult);
					return Common.OVERLAPPING_ENTRIES;
				}
			}

			statement = con.prepareStatement("INSERT INTO " + Common.coursesTable + " (" +
											 Common.courseNameField + "," +
											 Common.descriptionField + "," +
											 Common.creditPointsField + "," +
											 Common.capacityField + "," +
											 Common.alreadyRegisteredField + "," +
											 Common.groupNumberField + "," +
											 Common.courseCreatorField + ") VALUES (?,?,?,?,?,?,?)");
			statement.setString(1, newCourse.name);
			statement.setString(2, newCourse.description);
			statement.setFloat(3, newCourse.creditPoints);
			statement.setInt(4, newCourse.capacity);
			statement.setInt(5, newCourse.registered);
			statement.setInt(6, newCourse.groupNumber);
			statement.setString(7, newCourse.courseCreator);
			DatabaseManager.testPrint(statement.toString());
			statement.execute();
			
			/* Adding the sessions one by one */
			for (int i = 0 ; i < sessions.size() ; i++)
			{
				statement = con.prepareStatement("INSERT INTO " + Common.sessionsTable + " (" +
			                                     Common.courseNameField + "," +
			                                     Common.startingTimeField + "," +
			                                     Common.durationField + "," +
			                                     Common.endingTimeField + "," +
			                                     Common.dayField + "," +
			                                     Common.groupNumberField + ") VALUES (?,?,?,?,?,?)");
				statement.setString(1, sessions.get(i).courseName);
				statement.setInt(2, sessions.get(i).startingTime);
				statement.setInt(3, sessions.get(i).duration);
				statement.setInt(4, sessions.get(i).endingTime);
				statement.setInt(5, sessions.get(i).day);
				statement.setInt(6, sessions.get(i).groupNumber);
				DatabaseManager.testPrint(statement.toString());
				statement.execute();
			}			
			con.commit();
		}
		catch(SQLException e)
		{
			DatabaseManager.performRollback(con);
			return Common.INTERNAL_DB_ERROR;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, queryResult);
		}
		
		return Common.NO_ERROR;
	}
	
	public static int removeCourse(String courseName)
	{

		Connection con = null;
		PreparedStatement statement = null;
		ResultSet queryResult = null;
		
		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_REPEATABLE_READ, false);
			/* Locking the course row and all the related sessions? */
			statement = con.prepareStatement("SELECT * FROM " + Common.coursesTable + " WHERE " +
											 Common.courseNameField + "=? FOR UPDATE");
			statement.setString(1, courseName);
			DatabaseManager.testPrint(statement.toString());
			queryResult = statement.executeQuery();
			
			if (!queryResult.next())
			{
				/* Course doesn't exist */
				DatabaseManager.closeQuery(con, statement, queryResult);
				return Common.ENTRY_DOESNT_EXIST;
			}
			
			statement = con.prepareStatement("SELECT * FROM " + Common.sessionsTable + " WHERE " +
					                         Common.courseNameField + "=? FOR UPDATE");
			statement.setString(1, courseName);
			DatabaseManager.testPrint(statement.toString());
			statement.execute();

			statement = con.prepareStatement("SELECT * FROM " + Common.registeredTable + " WHERE " +
					                         Common.courseNameField + "=? FOR UPDATE");
			statement.setString(1, courseName);
			DatabaseManager.testPrint(statement.toString());
			statement.execute();
			
			/* Remove the course itself, foreign keys take care of everything... */
			statement = con.prepareStatement("DELETE FROM " + Common.coursesTable + " WHERE " +
					 Common.courseNameField + "=?");
			statement.setString(1, courseName);
			DatabaseManager.testPrint(statement.toString());		
			statement.execute();
			
			con.commit();
		}
		catch(SQLException e)
		{
			DatabaseManager.performRollback(con);
			return Common.INTERNAL_DB_ERROR;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, queryResult);
		}	
		
		return Common.NO_ERROR;
	}
}
